﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmTargetStaff

    Sub FillStaffList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select SNo,FullName,IsNull(ClientsProfile,N'') ClientsProfile,Branch From Users Order By SNo", cnn)
            Dim Reader As SqlDataReader

            Me.ListUsers.Items.Clear()
            Me.CombSalesman.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("SNo")
                Item.SubItems.Add(Reader.Item("FullName"))
                Item.SubItems.Add(Reader.Item("ClientsProfile"))
                Item.SubItems.Add(Reader.Item("Branch"))

                Me.ListUsers.Items.Add(Item)
                Me.CombSalesman.Items.Add(Reader.Item("FullName"))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try
    End Sub

    Private Sub ListUsers_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ListUsers.SelectedIndexChanged
        Me.txtSalesman.Clear()
        Me.GridBudget.Rows.Clear()

        If Me.ListUsers.SelectedItems.Count > 0 Then
            Me.txtSalesman.Text = Me.ListUsers.SelectedItems(0).SubItems(1).Text
            FillBudget()
        End If
    End Sub

    Sub FillBudget()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.GridBudget.Rows.Clear()

            Dim cmd As New SqlCommand
            cmd.Connection = cnn

            cnn.Open()
            For i As Integer = 1 To 12
                cmd.CommandText = "Select IsNull(dbo.GetPremTargetStaff(" & Me.DTPYear.Value.Year & "," & i & _
                                  ",N'" & Me.txtSalesman.Text.Trim & "'),0)"

                Me.GridBudget.Rows.Add(New String() {i, MonthName(i), CDbl(cmd.ExecuteScalar).ToString("N2")})
            Next
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub DTPYear_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DTPYear.ValueChanged
        If Me.ListUsers.SelectedItems.Count > 0 Then
            FillBudget()
        End If
    End Sub

    Private Sub frmTargetStaff_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillStaffList()
    End Sub

    Private Sub GridBudget_CellEndEdit(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridBudget.CellEndEdit
        Try
            Me.GridBudget.CurrentCell.Value = CDbl(Me.GridBudget.CurrentCell.Value).ToString("N2")
        Catch ex As Exception
            Me.GridBudget.CurrentCell.Value = "0.00"
        End Try
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        Me.ErrProv.Clear()

        If Me.ListUsers.SelectedItems.Count = 0 Then
            Me.ErrProv.SetError(Me.txtSalesman, "Please select a valid salesman from the list")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cmd.Connection = cnn

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans

                'Delete old data
                cmd.CommandText = "Delete From StaffTarget Where Year=" & Me.DTPYear.Value.Year & _
                                  " and SalesMan=N'" & Me.txtSalesman.Text.Trim & "'"
                cmd.ExecuteNonQuery()

                'Save new year budget
                For Each row As DataGridViewRow In Me.GridBudget.Rows
                    cmd.CommandText = "Insert Into StaffTarget (Year,Month,SalesMan,Target) " & _
                                      "Values (@Year,@Month,@SalesMan,@Target)"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@Year", Me.DTPYear.Value.Year)
                    cmd.Parameters.AddWithValue("@Month", row.Cells(0).Value)
                    cmd.Parameters.AddWithValue("@SalesMan", Me.txtSalesman.Text.Trim)
                    cmd.Parameters.AddWithValue("@Target", CDbl(row.Cells(2).Value))
                    cmd.ExecuteNonQuery()
                Next

                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully")

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnGenerate_Click(sender As System.Object, e As System.EventArgs) Handles btnGenerate.Click
        Me.ErrProv.Clear()

        If Me.CombSalesman.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombSalesman, "Please select a valid salesman from the list")

        Else
            If Me.RStatement.Checked = True Then
                ShowStatement()

            ElseIf Me.RSummary.Checked = True Then
                ShowSummary()
            End If
        End If
    End Sub

    Sub ShowStatement()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select * From Policies Where Producer=N'" & Me.CombSalesman.SelectedItem & "' " & _
                                        "and TransDate>=N'" & Me.DTPPeriodFRmRpt.Value.ToShortDateString & " 00:00:00' " & _
                                        "and TransDate<=N'" & Me.DTPPeriodToRpt.Value.ToShortDateString & " 23:59:59'", cnn)
            Dim das As New DataSet

            cnn.Open()
            dap.Fill(das, "Policies")
            cnn.Close()

            Dim rpt As New ProductionRetail
            rpt.SetDataSource(das)

            'Create Parameters

            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRmRpt.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodToRpt.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Producer PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Prod")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.CombSalesman.SelectedItem
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowSummary()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand
            Dim Reader As SqlDataReader
            Dim das As New DsStaffTarget

            cmd.Connection = cnn

            cnn.Open()
            For i As Integer = 1 To 12
                cmd.CommandText = "Select IsNull(dbo.GetPremTargetStaff(" & Me.DTPPeriodFRmRpt.Value.Year & "," & i & _
                                  ",N'" & Me.CombSalesman.Text.Trim & "'),0)," & _
                                  "IsNull(dbo.GetPremActualStaff(" & Me.DTPPeriodFRmRpt.Value.Year & "," & i & _
                                  ",N'" & Me.CombSalesman.Text.Trim & "'),0)"

                Reader = cmd.ExecuteReader
                While Reader.Read
                    das.Tables(0).Rows.Add(New String() {i, MonthName(i), CDbl(Reader.Item(0)), CDbl(Reader.Item(1))})
                End While
                Reader.Close()
            Next
            cnn.Close()

            Dim rpt As New TargetStaff
            rpt.SetDataSource(das)

            'Cr Parameter
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'Year PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Year")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPYear.Value.Year
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


            'SalesMan PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("SalesMan")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.CombSalesman.Text
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class